package org.spring.dao.impl;

import org.common.entity.Employee;
import org.spring.dao.IEmployeeDAO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

/**
 * @author yexi
 */
@Repository
public class EmployeeDAOImpl implements IEmployeeDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insert(Employee employee) {
        return this.insert(employee, true);
    }

    @Override
    public int insert(Employee employee, boolean idFlag) {
        String sql = "insert into employee values(DEFAULT,?,?,?,?,?,?,?)";
        if (idFlag) {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            int updateCount = jdbcTemplate.update(con -> {
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setObject(1, employee.getEmpName());
                ps.setObject(2, employee.getJob());
                ps.setObject(3, employee.getMgr());
                ps.setObject(4, employee.getHiredate());
                ps.setObject(5, employee.getSalary());
                ps.setObject(6, employee.getBonus());
                ps.setObject(7, employee.getDeptNo());
                return ps;
            }, keyHolder);
            employee.setEmpNo(keyHolder.getKey().intValue());
            return updateCount;
        } else {
            // TIPS: JdbcTemplate简化操作数据库的代价就是这样子返回的对象没有主键id
            return jdbcTemplate.update(sql, employee.getEmpName(), employee.getJob(), employee.getMgr(),
                    employee.getHiredate(), employee.getSalary(), employee.getBonus(), employee.getDeptNo());
        }
    }

    @Override
    public int deleteById(Long id) {
        String sql = "delete from employee where emp_no = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public int updateById(Employee employee) {
        String sql = "update employee set emp_name= ?,job=?,mgr=?,hiredate=?,salary=?,bonus=?,dept_no=? where emp_no = ?";
        Object[] args = {employee.getEmpName(), employee.getJob(), employee.getMgr(),
                employee.getHiredate(), employee.getSalary(), employee.getBonus(), employee.getDeptNo(), employee.getEmpNo()};
        return jdbcTemplate.update(sql, args);
    }

    @Override
    public Employee selectById(Long id) {
        String sql = "select * from employee where emp_no = ?";
        // RowMapper: 将结果集中的每一行数据封装到指定的类中
        BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        /**
         * @see DataAccessUtils#nullableSingleResult(Collection) 由于jdbcTemplate的特殊实现，查询为空或者多余1个都会抛出异常
         *                                                       所以解决办法：1、捕获异常(实际不推荐，这里demo这么写)
         *                                                                   2、重写jdbcTemplate(嫌麻烦这里就这样了)
         */
        Employee employee = null;
        try {
            employee = jdbcTemplate.queryForObject(sql, rowMapper, id);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        return employee;
    }

    @Override
    public List<Employee> selectAll() {
        String sql = "select * from employee";
        BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        List<Employee> employeeList = jdbcTemplate.query(sql, rowMapper);
        return employeeList;
    }

    @Override
    public int selectCount() {
        return jdbcTemplate.queryForObject("select count(*) from employee", int.class);
    }


    @Override
    public int[] batchInsert(List<Employee> employeeList) {
        String sql = "insert into employee values(DEFAULT,?,?,?,?,?,?,?)";
        List<Object[]> args = new ArrayList<>();
        for (Employee employee : employeeList) {
            Object[] arg = {employee.getEmpName(), employee.getJob(), employee.getMgr(),
                    employee.getHiredate(), employee.getSalary(), employee.getBonus(), employee.getDeptNo()};
            args.add(arg);
        }
        return jdbcTemplate.batchUpdate(sql, args);
    }

    @Override
    public int[] batchUpdate(List<Employee> employeeList) {
        String sql = "update employee set emp_name= ?,job=?,mgr=?,hiredate=?,salary=?,bonus=?,dept_no=? where emp_no = ?";
        List<Object[]> args = new ArrayList<>();
        for (Employee employee : employeeList) {
            Object[] arg = {employee.getEmpName(), employee.getJob(), employee.getMgr(),
                    employee.getHiredate(), employee.getSalary(), employee.getBonus(), employee.getDeptNo(), employee.getEmpNo()};
            args.add(arg);
        }
        return jdbcTemplate.batchUpdate(sql, args);
    }

    @Override
    public int[] batchDelete(List<Integer> idList) {
        String sql = "delete from employee where emp_no = ?";
        List<Object[]> args = new ArrayList<>();
        for (Integer id : idList) {
            Object[] arg = {id};
            args.add(arg);
        }
        return jdbcTemplate.batchUpdate(sql, args);
    }


}
